﻿using System;
using System.Windows.Interop;
using System.Xml.Serialization;
using System.Collections.ObjectModel;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices.Automation;

namespace SLCOMSqlClient
{
    /// <summary>
    /// Represents an SQL Database
    /// </summary>
    public static class COMSqlDatabase
    {
        /// <summary>
        /// Execute a non query against the database
        /// </summary>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        /// <returns>Number of rows affected by the query</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType)
        {
            return ExecuteNonQuery(connectionString, commandText, commandType, new SqlParameter[] { });
        }
        
        /// <summary>
        /// Execute a non query against the database
        /// </summary>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        ///<param name="parameters">List of parameters to pass, null for none</param>
        /// <returns>Number of rows affected by the query</returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType,
            IEnumerable<SqlParameter> parameters)
        {

            string compiledParameters = string.Empty;
            List<object> parameterValues = new List<object>();
            if (parameters != null && parameters.Count() > 0)
            {
                parameterValues.AddRange((from oneValue in parameters select oneValue.ParameterValue));
                compiledParameters = string.Join("|", (from oneValue in parameters select oneValue.ParameterName));
            }
            dynamic sqlDB = AutomationFactory.CreateObject("COMSQLClient.COMSqlDatabase");
            dynamic returnValue = sqlDB.ExecuteNonQuery(connectionString, commandText, commandType.ToString(), compiledParameters, parameterValues.ToArray());
            return (int)returnValue;
        }


        /// <summary>
        /// Execute a query and ge the reslt set
        /// </summary>
        /// <typeparam name="T">Type of an object which will be contained
        /// within the result set
        /// </typeparam>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        /// <returns>Objeservable collection of objects of type T</returns>
        public static ObservableCollection<T> ExecuteQuery<T>(
            string connectionString, string commandText, CommandType commandType)
        {
            return ExecuteQuery<T>(connectionString, commandText, commandType, new SqlParameter[] { });
        }


        /// <summary>
        /// Execute a query and ge the reslt set
        /// </summary>
        /// <typeparam name="T">Type of an object which will be contained
        /// within the result set
        /// </typeparam>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        ///<param name="parameters">List of parameters to pass, null for none</param>        
        /// <returns>Objeservable collection of objects of type T</returns>
        public static ObservableCollection<T> ExecuteQuery<T>(
            string connectionString, string commandText, CommandType commandType,
            IEnumerable<SqlParameter> parameters)
        {
            string compiledParameters = string.Empty;
            List<object> parameterValues = new List<object>();
            if (parameters != null && parameters.Count() > 0)
            {
                parameterValues.AddRange((from oneValue in parameters select oneValue.ParameterValue));
                compiledParameters = string.Join("|", (from oneValue in parameters select oneValue.ParameterName));
            }
            dynamic sqlDB = AutomationFactory.CreateObject("COMSQLClient.COMSqlDatabase");
            dynamic returnValue = sqlDB.ExecuteQuery(connectionString, commandText,
                commandType.ToString(), compiledParameters, parameterValues.ToArray());

            XmlSerializer serializer = new XmlSerializer(typeof(object[][]));
            System.IO.MemoryStream aStream = new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(returnValue[0]));
            string fieldsData = returnValue[1];
            string[] fields = fieldsData.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
            object[][] sqlData = (object[][])serializer.Deserialize(aStream);

            ObservableCollection<T> convertedData = new ObservableCollection<T>();
            Dictionary<string, PropertyInfo> properties = new Dictionary<string, PropertyInfo>();
            PropertyInfo propertyInfo;
            foreach (var name in fields)
            {
                propertyInfo = typeof(T).GetProperty(name);
                properties.Add(name, propertyInfo);
            }
            for (int rowCounter = 0; rowCounter < sqlData.GetLength(0); rowCounter++)
            {
                T row = Activator.CreateInstance<T>();
                int column = 0;
                foreach (var key in properties.Keys)
                {
                    if (properties[key] != null)
                    {
                        properties[key].SetValue(row, sqlData[rowCounter][column], null);
                    }
                    column++;
                }
                convertedData.Add(row);
            }
            return convertedData;
        }

        /// <summary>
        /// Execute scalar query against the database
        /// </summary>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        ///<param name="parameters">List of parameters to pass, null for none</param>
        /// <returns>Scalar value from the query</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType,
            IEnumerable<SqlParameter> parameters)
        {

            string compiledParameters = string.Empty;
            List<object> parameterValues = new List<object>();
            if (parameters != null && parameters.Count() > 0)
            {
                parameterValues.AddRange((from oneValue in parameters select oneValue.ParameterValue));
                compiledParameters = string.Join("|", (from oneValue in parameters select oneValue.ParameterName));
            }
            dynamic sqlDB = AutomationFactory.CreateObject("COMSQLClient.COMSqlDatabase");
            dynamic returnValue = sqlDB.ExecuteScalar(connectionString, commandText, commandType.ToString(), compiledParameters, parameterValues.ToArray());
            return returnValue;
        }

        /// <summary>
        /// Execute scalar query against the database
        /// </summary>
        /// <param name="connectionString">Connection string for database</param>
        /// <param name="commandText">Text of a command</param>
        /// <param name="commandType">Type of the command</param>
        /// <returns>Scalar value from the query</returns>
        public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType)
        {
            return ExecuteScalar(connectionString, commandText, commandType, new SqlParameter[] { });
        }
    }
}
